package preparedStatement;

import org.junit.Test;
import util.JDBCUtils;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Properties;

/**
 * 使用PreparedStatement来替换Statement，实现对数据表的 增删查改 操作
 **/
public class PreparedStatementUpdateTest {

    //向customers表中添加一条记录
    @Test
    public void Insert() {

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            //1.读取配置信息
            InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
            Properties properties = new Properties();
            properties.load(is);
            //获取配置文件中的四个值
            String user = properties.getProperty("user");
            String password = properties.getProperty("password");
            String url = properties.getProperty("url");
            String driverClass = properties.getProperty("driverClass");

            //2.加载驱动
            Class.forName(driverClass);
            //3.获取连接
            connection = DriverManager.getConnection(url, user, password);

            //4.预编译sql语句，返回PreparedStatement的实例
            String sql = "insert into customers(name, email, birth) values(?, ?, ?)";// ?：占位符
            preparedStatement = connection.prepareStatement(sql);

            //5.填充占位符
            preparedStatement.setString(1, "哪吒");
            preparedStatement.setString(2, "nezha@gmail.com");
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
            Date date = simpleDateFormat.parse("1000-01-01");
            java.sql.Date date1 = new java.sql.Date(date.getTime());
            preparedStatement.setDate(3, date1);

            //6.执行操作
            preparedStatement.execute();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //7.资源的关闭
            try {
                if (connection != null) {
                    connection.close();
                }
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    //修改customers表中的一条记录
    @Test
    public void Update() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            //1.获取数据库的连接
            connection = JDBCUtils.getConnection();
            //2.预编译SQL语句，返回preparedStatement的实例
            String sql = "update customers set name = ?, email = ?, birth= ? where id = ?";
            preparedStatement = connection.prepareStatement(sql);
            //3.填充占位符
            preparedStatement.setString(1, "敖丙");
            preparedStatement.setString(2, "aoBing@gmail.com");
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
            Date date = simpleDateFormat.parse("1000-02-02");
            java.sql.Date date1 = new java.sql.Date(date.getTime());
            preparedStatement.setDate(3, date1);
            preparedStatement.setInt(4, 21);
            //4.执行操作
            preparedStatement.execute();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //5.关闭资源
            JDBCUtils.closeResourceConnection(connection, preparedStatement);
        }
    }



    @Test
    public void testChange() {
        //删除表数据
        String sqlDelete = "delete from customers where id=?";
        change(sqlDelete, 21);
        //修改表数据
        String sqlUpdate = "update `customers` set name = ? where id = ?";
        change(sqlUpdate, "汪星人", "1");
    }
    //通用的增删改操作
    public void change(String sql, Object ...args) { //sql占位符的个数与可变形参的长度一样
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            //1.获取数据库的连接
            connection = JDBCUtils.getConnection();
            //2.预编译SQL语句，返回preparedStatement实例
            preparedStatement = connection.prepareStatement(sql);
            //3.填充占位符
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i+1, args[i]);
            }
            //4.执行操作
            preparedStatement.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //5.资源的关闭
            JDBCUtils.closeResourceConnection(connection, preparedStatement);
        }
    }
}
